<?php 

	/***************************\
	*							*
	*	Database management.	*
	*							*
	\***************************/

	//Database connection.
	/*function dbConnection()
	{
		try
		{
			$db=new PDO(MYSQL_HOST, USER, PWD);
			$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		}
		catch(PDOException $e)
		{
		  echo 'Connection error with the database: ' . $e->getMessage();
		}
		return $db;
	}*/

	//Prepare our datetime to SQL datetime format.
	function dateTimeFormat($dateTime)
	{
		return date('Y-m-d H:i:s', strtotime($dateTime));
	}
	
	function getDAO()
	{
		$db=new PDO(MYSQL_HOST, USER, PWD);
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		return $db;
	}
	
	//Insert the CSV information into our DB.- ARREGLAR -
	function dataCSV($dataStruct)
	{
		try
		{
			$db = getDAO();

			$truncate = 'TRUNCATE TABLE '.DATA_CSV_TABLE.'';//BORRAR-------------------------Cuando termine las pruebas
			$sentence = $db->query($truncate);

			$registro = array();
			foreach ($dataStruct as $registro) {
				$dateTime = dateTimeFormat($registro["Closed"]);

				$sql = $db->prepare("INSERT INTO ".DATA_CSV_TABLE." (".ID_FIELD .",".OWNER_FIELD.",".CLOSED_FIELD.",".CLOSEDTIMESTAMP_FIELD.",".SCORE_FIELD.",".CC_FIELD.") VALUES (:id, :owner, :closed, :timestamp, :score, :cc)");
				$sql -> execute( array( ':id' => (int)$registro["ID"], ':owner' => $registro["Owner"], ':closed' => $dateTime, ':timestamp' => (int)$registro["Timestamp"], ':score' => (int)$registro["Score"], ':cc' => $registro["Cc"]));
				//var_dump($registro);
			}

				closeDataBaseConnection();
		}
		catch(PDOException $e)
		{
			echo("Connection error with the database: " . $e->getMessage());
		}
	}
	
	function getRankingList($prizeId)
	{
		$db = getDAO();

		// filters:
		// - closed
		// groups
		// - owner
		// group calculations
		// - sum of all scores
		// item id:
		// - owner
		// order:
		// - score descendent
		$rankingListQuery = 'SELECT ROUND(SUM(SCORES.contributorScore)) as contributorScore, SCORES.alias
							FROM
							((SELECT SUM(score) as contributorScore, qr_contributors.alias
							 FROM qr_datacsv 
								INNER JOIN qr_contributors ON qr_datacsv.owner = qr_contributors.originalName
							 WHERE closed >= (SELECT startDate 
											 FROM qr_prizes
											 WHERE qr_prizes.id  = '.$prizeId.')
								AND closed <= (SELECT endDate
											FROM qr_prizes
											WHERE qr_prizes.id  = '.$prizeId.')
								AND (livingDate >= (SELECT endDate
											FROM qr_prizes
											WHERE qr_prizes.id  = '.$prizeId.')
									OR
									 livingDate IS NULL)
							GROUP BY owner)
							UNION
							(SELECT SUM(score * 0.5) as contributorScore, qr_contributors.alias
							 FROM qr_datacsv 
								INNER JOIN qr_contributors ON qr_datacsv.Cc = qr_contributors.originalName
							 WHERE closed >= (SELECT startDate 
											 FROM qr_prizes
											 WHERE qr_prizes.id  = '.$prizeId.')
								AND closed <= (SELECT endDate
											FROM qr_prizes
											WHERE qr_prizes.id  = '.$prizeId.')
								AND (livingDate >= (SELECT endDate
											FROM qr_prizes
											WHERE qr_prizes.id  = '.$prizeId.')
									OR
									 livingDate IS NULL)
							 GROUP BY Cc)) AS SCORES
							 GROUP BY SCORES.alias
							 ORDER BY contributorScore DESC';
		$queryRanking = $db->query($rankingListQuery);
		
		$rankingList = array();
		while($register = $queryRanking->fetch())
		{
			$rankingList[] = array("Score" => $register["contributorScore"], "Name" => $register["alias"]);
			echo $register["contributorScore"];
			echo $register["alias"];
		}

		closeDataBaseConnection();
		
		return $rankingList;
	}
	
	function getHistoryTree()
	{
		$db = getDAO();

		$historyQuery = 'SELECT qr_prizes.id, startDate, endDate, title, groupId, name
							 FROM qr_prizes
							 INNER JOIN qr_prizegroups ON qr_prizes.groupId = qr_prizegroups.id
							 ORDER BY qr_prizegroups.id, startDate';
		$queryHistory = $db->query($historyQuery);
		
		$historyTree = array();
		
		while($register = $queryHistory->fetch())
		{
			$groupId = $register["groupId"];
			
			$prizeInfo = array("Name" => $register["title"], 
							   "StartDate" => date('d-m-Y', strtotime($register["startDate"])), 
							   "EndDate" => date('d-m-Y', strtotime($register["endDate"])), 
							   "Id" => $register["id"]);
			$historyTree[] = array("GroupName" => $register["name"], "PrizeInfo" => $prizeInfo);
			
			/*echo $register["title"];
			echo $register["startDate"];
			echo $register["endDate"];
			echo $register["qr_prizes.id"];
			echo $register["namePrizeGroup"];
			echo $register["qr_prizegroups.id"];*/
		}

		closeDataBaseConnection();
		
		return $historyTree;
	}

	function getPrize($prizeId)
	{
		$db = getDAO();

		$prizeQuery = 'SELECT id, startDate, endDate, title, description, rewardedPercentage, imageUrl, requiredScore
					   FROM qr_prizes
					   WHERE id = '.$prizeId;
		$queryPrize = $db->query($prizeQuery);
		
		$prize = null;
		
		if($register = $queryPrize->fetch())
		{
			$prize = array(	"Name" => $register["title"], 
							"StartDate" => date('d-m-Y', strtotime($register["startDate"])), 
							"EndDate" => date('d-m-Y', strtotime($register["endDate"])), 
							"Id" => $register["id"], 
							"Description" => $register["description"],
							"RewardedPercentage" => $register["rewardedPercentage"],
							"ImageUrl" => $register["imageUrl"],
							"RequiredScore" => $register["requiredScore"]);
			/*echo $register["title"];
			echo $register["startDate"];
			echo $register["endDate"];
			echo $register["id"];
			echo $register["description"];
			echo $register["rewardedPercentage"];
			echo $register["imageUrl"];
			echo $register["requiredScore"];
			*/
		}

		closeDataBaseConnection();
		
		return $prize;
	}
	
	function getGroupFromPrize($prizeId)
	{
		$db = getDAO();

		$groupQuery = 'SELECT qr_prizegroups.id, name
							 FROM qr_prizegroups
							 INNER JOIN qr_prizes ON qr_prizegroups.id = qr_prizes.groupId
							 WHERE qr_prizes.id = '.$prizeId.' 
							 GROUP BY qr_prizegroups.id';
		$queryGroup = $db->query($groupQuery);
		
		$prizeGroup = null;
		
		if($register = $queryGroup->fetch())
		{
			$prizeGroup = array(	"Id" => $register["id"], 
									"Name" => $register["name"]);
			//echo $register["id"];
			//echo $register["name"];
		}

		closeDataBaseConnection();
		
		return $prizeGroup;
	}
	
	function getCurrentPrize()
	{
		// Get prize for the current period based on present date
		$db = getDAO();

		$prizeQuery = 'SELECT id
					   FROM qr_prizes
					   WHERE startDate <= "'.date("Y-m-d H:i:s").'" AND endDate >= "'.date("Y-m-d H:i:s").'"';
		$queryPrize = $db->query($prizeQuery);
		
		$prize = null;
		
		if($register = $queryPrize->fetch())
		{
			$prize = $register["id"];
			
			//echo $register["id"];
		}

		closeDataBaseConnection();
		
		return $prize;
	}
	
	//Insert the valid session token into our DB.
	function insertSessionDB($token_auth)
	{
		if(isset($token_auth)){
			try
			{
				$db = getDAO();

				$truncate = 'TRUNCATE TABLE '.SESSION_TABLE.'';
				$sentence = $db->query($truncate);

				$sql = $db->prepare("INSERT INTO ".SESSION_TABLE." (".SESSION_TOKEN_FIELD.") VALUES (:token_auth)");
				$sql -> execute( array( ':token_auth' => $token_auth));

				closeDataBaseConnection();
			}
			catch(PDOException $e)
			{
			  	echo 'Connection error with the database: ' . $e->getMessage();
			}
		}
	}

	//Read the valid session token.
	function readSessionDB()
	{
		try
			{
				$db = getDAO();

				$sql = $db->prepare('SELECT * FROM '.SESSION_TABLE.'');
  				$sql->execute();
				$datos = $sql->fetch();
    			echo $datos[0] . '<br/>';

    			closeDataBaseConnection();
		}
		catch(PDOException $e)
		{
		  	echo 'Connection error with the database: ' . $e->getMessage();
		}
	}

	function closeDataBaseConnection(){
		try {
			unset($db);
		} catch (PDOException $e) {
			echo "Error closing the connection: " .$e->getMessage();
		}
	}
?>